package com.wits.qso.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.Query;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.stereotype.Component;

import com.wits.platform.dao.impl.BaseDaoImpl;
import com.wits.qso.dao.custom.CourseTeacherViewDaoCustom;
import com.wits.qso.entity.CourseTeacherView;
import com.wits.qso.pageable.NativeSqlPageable;

/**
 * 课程老师视图数据操作实现
 */
@Component
public class CourseTeacherViewDaoImpl extends BaseDaoImpl<CourseTeacherView, String>
		implements CourseTeacherViewDaoCustom {

	@SuppressWarnings("unchecked")
	public List<CourseTeacherView> findCourseTeacherViews(
			Map<String, Object> param) {
		if (param == null) {
			return new ArrayList<CourseTeacherView>();
		}
		StringBuffer buf = new StringBuffer(" select distinct a.course_id,a.course_code,a.course_title,a.course_date_f,a.course_date_t,a.course_period,a.course_lst_id,a.user_cre,a.date_cre,a.user_upd,a.date_upd,a.remark,c.ter_id,c.ter_name from qso_course a left join qso_btable b on a.course_id=b.btable_couid  join qso_teacher c on b.btable_teaid=c.ter_id where 1=1  ");

		if(param.get("course_id")!=null){
			buf.append(" and a.course_id=:course_id ");
		}
		if(param.get("course_code")!=null){
			buf.append(" and a.course_code=:course_code ");
		}
		if(param.get("course_title")!=null){
			buf.append(" and a.course_title like %:course_title% ");
		}
		if(param.get("course_period")!=null){
			buf.append(" and a.course_period=:course_period ");
		}
		if(param.get("course_date_f")!=null){
			buf.append(" and a.course_date_f=:course_date_f ");
		}
		if(param.get("course_date_t")!=null){
			buf.append(" and a.course_date_t =:course_date_t ");
		}
		if(param.get("course_lst_id")!=null){
			buf.append(" and a.course_lst_id =:course_lst_id ");
		}
		if(param.get("ter_id")!=null){
			buf.append(" and c.ter_id =:ter_id ");
		} 
		if(param.get("ter_name")!=null){
			buf.append(" and a.ter_name like %:ter_name% ");
		}
		
		Query query = em.createNativeQuery(buf.toString(),CourseTeacherView.class);
		if(param.get("course_id")!=null){
			query.setParameter("course_id", param.get("course_id"));
		}
		if(param.get("course_code")!=null){
			query.setParameter("course_code", param.get("course_code"));
		}
		if(param.get("course_title")!=null){
			query.setParameter("course_title", param.get("course_title"));
		}
		if(param.get("course_period")!=null){
			query.setParameter("course_period", param.get("course_period"));
		}
		if(param.get("course_date_f")!=null){
			query.setParameter("course_date_f", param.get("course_date_f"));
		}
		if(param.get("course_date_t")!=null){
			query.setParameter("course_date_t", param.get("course_date_t"));
		}
		if(param.get("course_lst_id")!=null){
			query.setParameter("course_lst_id", param.get("course_lst_id"));
		}
		if(param.get("ter_id")!=null){
			query.setParameter("ter_id", param.get("ter_id"));
		} 
		if(param.get("ter_name")!=null){
			query.setParameter("ter_name", param.get("ter_name"));
		}
		@SuppressWarnings("rawtypes")
		List list=query.getResultList();
		return 	list;
	}


	/**
	 * 带分页的查询
	 * @param param
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page<CourseTeacherView> findCourseTeacherViewPage(
			Map<String, Object> param) {
		PageImpl<CourseTeacherView> pipl=null;
		if (param == null) {
			 return pipl;
		}
		
		StringBuffer buf = new StringBuffer(" select distinct a.course_id,a.course_code,a.course_title,a.course_date_f,a.course_date_t,a.course_period,a.course_lst_id,a.user_cre,a.date_cre,a.user_upd,a.date_upd,a.remark,c.ter_id,c.ter_name from qso_course a left join qso_btable b on a.course_id=b.btable_couid  join qso_teacher c on b.btable_teaid=c.ter_id where 1=1  ");

		if(param.get("course_id")!=null){
			buf.append(" and a.course_id=:course_id ");
		}
		if(param.get("course_code")!=null){
			buf.append(" and a.course_code=:course_code ");
		}
		if(param.get("course_title")!=null){
			buf.append(" and a.course_title like %:course_title% ");
		}
		if(param.get("course_period")!=null){
			buf.append(" and a.course_period=:course_period ");
		}
		if(param.get("course_date_f")!=null){
			buf.append(" and a.course_date_f=:course_date_f ");
		}
		if(param.get("course_date_t")!=null){
			buf.append(" and a.course_date_t =:course_date_t ");
		}
		if(param.get("course_lst_id")!=null){
			buf.append(" and a.course_lst_id =:course_lst_id ");
		}
		if(param.get("ter_id")!=null){
			buf.append(" and c.ter_id =:ter_id ");
		} 
		if(param.get("ter_name")!=null){
			buf.append(" and a.ter_name like %:ter_name% ");
		}
		//查询总数
		StringBuffer sqlBuf00=new StringBuffer("");
		sqlBuf00.append(buf);
		sqlBuf00.append(" ");
		//分页查询起始行，每页行数
		StringBuffer sqlBuf01=new StringBuffer();
		sqlBuf01.append(buf);
		String[] orderByArray=(String[])param.get("orderByArray");
		if(orderByArray!=null&&orderByArray.length>0){
			sqlBuf01.append(" order by ");
			for(int i=0;i<orderByArray.length;i++){
				String str=orderByArray[i];
				if(str!=null){
					if(i>0){
						sqlBuf01.append(",");
					}
					sqlBuf01.append(str);
				}
			}
		}
		sqlBuf01.append(" limit :startLine,:lineEveryPage ");
		
		Query query00 = em.createNativeQuery(sqlBuf00.toString(),CourseTeacherView.class);
		Query query = em.createNativeQuery(sqlBuf01.toString(),CourseTeacherView.class);
		if(param.get("course_id")!=null){
			query00.setParameter("course_id", param.get("course_id"));
			query.setParameter("course_id", param.get("course_id"));
		}
		if(param.get("course_code")!=null){
			query00.setParameter("course_code", param.get("course_code"));
			query.setParameter("course_code", param.get("course_code"));
		}
		if(param.get("course_title")!=null){
			query00.setParameter("course_title", param.get("course_title"));
			query.setParameter("course_title", param.get("course_title"));
		}
		if(param.get("course_period")!=null){
			query00.setParameter("course_period", param.get("course_period"));
			query.setParameter("course_period", param.get("course_period"));
		}
		if(param.get("course_date_f")!=null){
			query00.setParameter("course_date_f", param.get("course_date_f"));
			query.setParameter("course_date_f", param.get("course_date_f"));
		}
		if(param.get("course_date_t")!=null){
			query00.setParameter("course_date_t", param.get("course_date_t"));
			query.setParameter("course_date_t", param.get("course_date_t"));
		}
		if(param.get("course_lst_id")!=null){
			query00.setParameter("course_lst_id", param.get("course_lst_id"));
			query.setParameter("course_lst_id", param.get("course_lst_id"));
		}
		if(param.get("ter_id")!=null){
			query00.setParameter("ter_id", param.get("ter_id"));
			query.setParameter("ter_id", param.get("ter_id"));
		} 
		if(param.get("ter_name")!=null){
			query00.setParameter("ter_name", param.get("ter_name"));
			query.setParameter("ter_name", param.get("ter_name"));
		}
		
		NativeSqlPageable ctp=getNativeSqlPageable(param);
		int pageNum=ctp.getPageNumber();
		int pageSize=ctp.getPageSize();
		query.setParameter("startLine", (pageNum-1)*pageSize);
		query.setParameter("lineEveryPage", pageSize);
		
		//查询总数
		int total=query00.getResultList().size();
		//查询记录
		@SuppressWarnings("rawtypes")
		List list=query.getResultList();
		
		pipl=new PageImpl<CourseTeacherView>(list, ctp, total);
		return 	pipl;
	}
	
	private NativeSqlPageable getNativeSqlPageable(Map<String,Object> param){
		NativeSqlPageable ctp=new NativeSqlPageable();
		Integer pageNumber=param.get("pageNumber")==null?new Integer(1):(Integer)param.get("pageNumber");
		Integer pageSize=param.get("pageSize")==null?new Integer(20):(Integer)param.get("pageSize");
		ctp.setPageNumber(pageNumber.intValue());
		ctp.setPageSize(pageSize.intValue());
		return ctp;
	}
}
